Public Class Class_CarResMasterData

    Public Function Search_CarReserveInfoNew(Optional ByVal ParaReserveId As String = "", _
Optional ByVal ParaReserveDate As String = "", _
Optional ByVal ResserveStatus As String = "", _
Optional ByVal UnitCode As String = "", _
Optional ByVal UserCode As String = "", _
Optional ByVal ApproveCode As String = "", _
Optional ByVal WareaGroupId As String = "", _
Optional ByVal AgentId As String = "", _
Optional ByVal StrConn As String = "", _
Optional ByVal Start_Date As String = "", _
Optional ByVal Stop_Date As String = "") As DataSet

        Try
            Dim CLS As New Class_Connection
            Dim Conn As OleDb.OleDbConnection
            Dim dSRet As DataSet
            Dim sTr As String = ""
            Dim cH As String = ""
            Dim cOndition As String = ""


            sTr &= " SELECT  *  FROM  InvReserveInfo"
            If ParaReserveId & "" <> "" Then
                If cOndition & "" <> "" Then
                    cOndition &= " and "
                End If
                cOndition &= "ReserveId like '%" & ParaReserveId & "%'"
            End If


            If ParaReserveDate & "" <> "" Then
                If cOndition & "" <> "" Then
                    cOndition &= " and "
                End If
                cOndition &= "ReserveDate like '%" & ParaReserveDate & "%'"
            End If

            If ResserveStatus & "" <> "" Then
                If cOndition & "" <> "" Then
                    cOndition &= " and "
                End If
                cOndition &= "UserStatus = '" & ResserveStatus & "'"
            End If

            If UnitCode & "" <> "" Then
                If cOndition & "" <> "" Then
                    cOndition &= " and "
                End If
                If Right(UnitCode.Replace("'", ""), 7) = "0000000" Then
                    cOndition &= "  UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 1) & "%' "
                ElseIf Right(UnitCode.Replace("'", ""), 6) = "000000" Then
                    cOndition &= "  UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 2) & "%' "
                ElseIf Right(UnitCode.Replace("'", ""), 4) = "0000" Then
                    cOndition &= "    UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 4) & "%' "
                ElseIf Right(UnitCode.Replace("'", ""), 3) = "000" Then
                    cOndition &= "  UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 5) & "%' "
                Else
                    cOndition &= "   UnitId = '" & UnitCode & "' "
                End If
                'Condition &= "(UnitId = " & UnitCode & ")"

            End If

            If UserCode & "" <> "" Then
                cH = "1"
                If cOndition & "" <> "" Then
                    cOndition &= " and "
                End If
                cOndition &= " (UserId = '" & UserCode & "'"
                If AgentId & "" <> "" Then
                    If cOndition & "" <> "" Then
                        cOndition &= " or "
                    End If
                    cOndition &= " AgentId = '" & AgentId & "')"
                Else
                    cOndition &= " )"
                End If
            Else
                cH = ""
            End If
            If cH = "" Then
                If AgentId & "" <> "" Then
                    If cOndition & "" <> "" Then
                        cOndition &= " and"
                    End If
                    cOndition &= " AgentId = '" & AgentId & "'"
                End If
            End If

            If ApproveCode & "" <> "" Then
                If cOndition & "" <> "" Then
                    cOndition &= " and "
                End If
                cOndition &= " ApproveId = '" & ApproveCode & "'"
            End If

            If WareaGroupId & "" <> "" Then
                If cOndition & "" <> "" Then
                    cOndition &= " and "
                End If
                cOndition &= "WareaGroupId = '" & WareaGroupId & "'"
            End If

            If Start_Date & "" <> "" And Stop_Date & "" <> "" Then
                If cOndition & "" <> "" Then
                    cOndition &= " and "
                End If
                cOndition &= "(Substring(StartDate,1,10) >= '" & Start_Date & "' AND Substring(StartDate,1,10) <= '" & Stop_Date & "')"
            End If



            If cOndition & "" <> "" Then
                sTr &= " where  "
                sTr &= cOndition
            End If


            sTr &= " Order by Substring(InvReserveInfo.StartDate,1,10) asc"
            Conn = CLS.ConnectionOpen(StrConn)
            dSRet = CLS.BindData(Conn, , , sTr)
            CLS.ConnectionClose(Conn)

            If Not dSRet Is Nothing Then
                Return dSRet
            Else
                Return Nothing
            End If

        Catch ex As Exception

            Return Nothing
        End Try





    End Function

    Public Function Search_CloseWorkNew(Optional ByVal ParaReserveId As String = "", _
    Optional ByVal ParaReserveDate As String = "", _
    Optional ByVal ResserveStatus As String = "", _
    Optional ByVal UnitCode As String = "", _
    Optional ByVal UserCode As String = "", _
    Optional ByVal ApproveCode As String = "", _
    Optional ByVal StrConn As String = "") As DataSet
        Dim CLS As New Class_Connection
        Dim Conn As OleDb.OleDbConnection
        Dim dSRet As DataSet
        Dim sTr As String = ""
        Dim cH As String = ""
        Dim cOndition As String = ""
        Dim sTrSql As String = ""

        Try


            sTr &= " SELECT DISTINCT InvReserveInfo.* "
            sTr &= " FROM         InvReserveInfo INNER JOIN"
            sTr &= " InvReserveDataCarDriver ON InvReserveInfo.ReserveId = InvReserveDataCarDriver.ReserveId "
            If UnitCode <> "" Then
                If Right(UnitCode.Replace("'", ""), 7) = "0000000" Then
                    cOndition &= " and (InvReserveInfo.UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 1) & "%' )"
                ElseIf Right(UnitCode.Replace("'", ""), 6) = "000000" Then
                    cOndition &= " and  (InvReserveInfo.UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 2) & "%' )"
                ElseIf Right(UnitCode.Replace("'", ""), 4) = "0000" Then
                    cOndition &= "  and  (InvReserveInfo.UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 4) & "%' )"
                ElseIf Right(UnitCode.Replace("'", ""), 3) = "000" Then
                    cOndition &= " and  (InvReserveInfo.UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 5) & "%' )"
                Else
                    cOndition &= "  and  (InvReserveInfo.UnitId = " & UnitCode & " )"
                End If
            End If
            If cOndition & "" <> "" Then
                sTr &= " where  (InvReserveDataCarDriver.CostWorkStatus = 0) "
                sTr &= cOndition
            End If

            Conn = CLS.ConnectionOpen(StrConn)
            dSRet = CLS.BindData(Conn, , , sTr)
            CLS.ConnectionClose(Conn)

            If Not dSRet Is Nothing Then
                Return dSRet
            Else
                Return Nothing
            End If

        Catch ex As Exception

        End Try

    End Function

    Function Show_DataForCloseWorkNew(Optional ByVal ParaReserveId As String = "", _
    Optional ByVal ParaReserveDate As String = "", _
    Optional ByVal ResserveStatus As String = "", _
    Optional ByVal UnitCode As String = "", _
    Optional ByVal UserCode As String = "", _
    Optional ByVal ApproveCode As String = "", _
    Optional ByVal WareaGroup As String = "", _
    Optional ByVal StrConn As String = "", _
    Optional ByVal Start_Date As String = "", _
    Optional ByVal Stop_Date As String = "", _
    Optional ByVal BD_NUMBER As String = "") As DataSet

        Dim CLS As New Class_Connection
        Dim Conn As OleDb.OleDbConnection
        Dim dSRet As DataSet
        Dim sTr As String = ""
        Dim cH As String = ""
        Dim cOndition As String = ""
        Dim sTrSql As String = ""

        Try
            sTrSql = " SELECT     InvReserveDataCarDriver.BdNumber, InvReserveDataCarDriver.WorkCost, InvReserveInfo.ReserveId, InvReserveInfo.UserId, "
            sTrSql = sTrSql & "   InvReserveInfo.StartDate, InvReserveInfo.StopDate, InvReserveDataCarDriver.CostWorkStatus, InvReserveInfo.Area  , "
            sTrSql = sTrSql & "   InvReserveInfo.UnitId,InvReserveInfo.UserStatus "
            sTrSql = sTrSql & " FROM InvReserveDataCarDriver INNER JOIN"
            sTrSql = sTrSql & "  InvReserveInfo ON InvReserveDataCarDriver.ReserveId = InvReserveInfo.ReserveId"
            sTrSql = sTrSql & "  where  (InvReserveDataCarDriver.CostWorkStatus = 0) "
            If UnitCode <> "" Then
                If Right(UnitCode.Replace("'", ""), 7) = "0000000" Then
                    cOndition &= " and (InvReserveInfo.UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 1) & "%' )"
                ElseIf Right(UnitCode.Replace("'", ""), 6) = "000000" Then
                    cOndition &= " and  (InvReserveInfo.UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 2) & "%' )"
                ElseIf Right(UnitCode.Replace("'", ""), 4) = "0000" Then
                    cOndition &= "  and  (InvReserveInfo.UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 4) & "%' )"
                ElseIf Right(UnitCode.Replace("'", ""), 3) = "000" Then
                    cOndition &= " and  (InvReserveInfo.UnitId LIKE '" & Left(UnitCode.Replace("'", ""), 5) & "%' )"
                Else
                    cOndition &= "  and  (InvReserveInfo.UnitId = " & UnitCode & " )"
                End If
            End If

            If ParaReserveId <> "" And BD_NUMBER <> "" Then
                sTrSql = sTrSql & "  AND  (InvReserveDataCarDriver.ReserveId Like '%" & ParaReserveId & "%' OR InvReserveDataCarDriver.BdNumber Like '%" & BD_NUMBER & "%' ) "
            ElseIf ParaReserveId <> "" And BD_NUMBER = "" Then
                sTrSql = sTrSql & "  AND  (InvReserveDataCarDriver.ReserveId Like '%" & ParaReserveId & "%' )"
            ElseIf ParaReserveId = "" And BD_NUMBER <> "" Then
                sTrSql = sTrSql & "  AND  (InvReserveDataCarDriver.BdNumber Like '%" & BD_NUMBER & "%') "
            End If

            If Start_Date & "" <> "" And Stop_Date & "" <> "" Then
                sTrSql = sTrSql & " AND  ((Substring(InvReserveInfo.StartDate,1,10) >= '" & Start_Date & "') AND (Substring(InvReserveInfo.StartDate,1,10) <= '" & Stop_Date & "'))"
            End If

            If WareaGroup <> "" Then
                sTrSql = sTrSql & " and  (InvReserveInfo.WareaGroupId = '" & WareaGroup & "') "
            End If


            sTrSql = sTrSql & "  " & cOndition

            sTrSql = sTrSql & " order by Substring(InvReserveInfo.StartDate,1,10) asc "

            sTr &= " Order by Substring(InvReserveInfo.StartDate,1,10) asc"
            Conn = CLS.ConnectionOpen(StrConn)
            dSRet = CLS.BindData(Conn, , , sTr)
            CLS.ConnectionClose(Conn)

            If Not dSRet Is Nothing Then
                Return dSRet
            Else
                Return Nothing
            End If

        Catch ex As Exception

            Return Nothing
        End Try




    End Function
End Class
